/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     12/21/2009 11:35:53 AM                       */
/*==============================================================*/

-- CTRL+A F5: drop DB and recreate everything
USE master
GO
IF DB_ID('Logistics') IS NOT NULL
DROP DATABASE Logistics
GO

CREATE DATABASE Logistics
GO
USE Logistics
GO
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNT') and o.name = 'FK_ACCOUNT_ACCOUNT_W_BRANCH')
alter table ACCOUNT
   drop constraint FK_ACCOUNT_ACCOUNT_W_BRANCH
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BRANCH') and o.name = 'FK_BRANCH_CITY_HAS__CITY')
alter table BRANCH
   drop constraint FK_BRANCH_CITY_HAS__CITY
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CUSTOMER') and o.name = 'FK_CUSTOMER_BELONG_TO_CITY')
alter table CUSTOMER
   drop constraint FK_CUSTOMER_BELONG_TO_CITY
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DELIVERY') and o.name = 'FK_DELIVERY_BOOK_BOOK_CUSTOMER')
alter table DELIVERY
   drop constraint FK_DELIVERY_BOOK_BOOK_CUSTOMER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DELIVERY') and o.name = 'FK_DELIVERY_DELIVERY__BRANCH')
alter table DELIVERY
   drop constraint FK_DELIVERY_DELIVERY__BRANCH
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DELIVERY') and o.name = 'FK_DELIVERY_START_AT__BRANCH')
alter table DELIVERY
   drop constraint FK_DELIVERY_START_AT__BRANCH
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PACKAGE') and o.name = 'FK_PACKAGE_INCLUDE_B_DELIVERY')
alter table PACKAGE
   drop constraint FK_PACKAGE_INCLUDE_B_DELIVERY
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ROUTE') and o.name = 'FK_ROUTE_ROUTE_END_WAREHOUS')
alter table ROUTE
   drop constraint FK_ROUTE_ROUTE_END_WAREHOUS
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ROUTE') and o.name = 'FK_ROUTE_ROUTE_FRO_WAREHOUS')
alter table ROUTE
   drop constraint FK_ROUTE_ROUTE_FRO_WAREHOUS
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRIP') and o.name = 'FK_TRIP_VEHICLE_U_VEHICLE')
alter table TRIP
   drop constraint FK_TRIP_VEHICLE_U_VEHICLE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRIP_PACKAGE') and o.name = 'FK_TRIP_PAC_PACKAGE_T_PACKAGE')
alter table TRIP_PACKAGE
   drop constraint FK_TRIP_PAC_PACKAGE_T_PACKAGE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRIP_PACKAGE') and o.name = 'FK_TRIP_PAC_TRIP_HAS__TRIP')
alter table TRIP_PACKAGE
   drop constraint FK_TRIP_PAC_TRIP_HAS__TRIP
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRIP_ROUTE') and o.name = 'FK_TRIP_ROU_ROUTE_TRA_ROUTE')
alter table TRIP_ROUTE
   drop constraint FK_TRIP_ROU_ROUTE_TRA_ROUTE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TRIP_ROUTE') and o.name = 'FK_TRIP_ROU_TRIP_TRAN_TRIP')
alter table TRIP_ROUTE
   drop constraint FK_TRIP_ROU_TRIP_TRAN_TRIP
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('VEHICLE') and o.name = 'FK_VEHICLE_BRANCH_OW_BRANCH')
alter table VEHICLE
   drop constraint FK_VEHICLE_BRANCH_OW_BRANCH
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('WAREHOUSE') and o.name = 'FK_WAREHOUS_BRANCH_HA_BRANCH')
alter table WAREHOUSE
   drop constraint FK_WAREHOUS_BRANCH_HA_BRANCH
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('WAREHOUSE') and o.name = 'FK_WAREHOUS_WAREHOUSE_PACKAGE')
alter table WAREHOUSE
   drop constraint FK_WAREHOUS_WAREHOUSE_PACKAGE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNT')
            and   name  = 'ACCOUNT_WORK_AT_BRANCH_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNT.ACCOUNT_WORK_AT_BRANCH_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ACCOUNT')
            and   type = 'U')
   drop table ACCOUNT
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('BRANCH')
            and   name  = 'CITY_HAS_BRANCH_FK'
            and   indid > 0
            and   indid < 255)
   drop index BRANCH.CITY_HAS_BRANCH_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BRANCH')
            and   type = 'U')
   drop table BRANCH
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CITY')
            and   type = 'U')
   drop table CITY
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CUSTOMER')
            and   name  = 'BELONG_TO_HAS_FK'
            and   indid > 0
            and   indid < 255)
   drop index CUSTOMER.BELONG_TO_HAS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CUSTOMER')
            and   type = 'U')
   drop table CUSTOMER
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DELIVERY')
            and   name  = 'BOOK_BOOK_BY_FK'
            and   indid > 0
            and   indid < 255)
   drop index DELIVERY.BOOK_BOOK_BY_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DELIVERY')
            and   name  = 'DELIVERY_HAS_DETINATION_AT_BRANCH_FK'
            and   indid > 0
            and   indid < 255)
   drop index DELIVERY.DELIVERY_HAS_DETINATION_AT_BRANCH_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DELIVERY')
            and   name  = 'START_AT_START_POINT_FK'
            and   indid > 0
            and   indid < 255)
   drop index DELIVERY.START_AT_START_POINT_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DELIVERY')
            and   type = 'U')
   drop table DELIVERY
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PACKAGE')
            and   name  = 'INCLUDE_BELONG_TO_FK'
            and   indid > 0
            and   indid < 255)
   drop index PACKAGE.INCLUDE_BELONG_TO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PACKAGE')
            and   type = 'U')
   drop table PACKAGE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ROUTE')
            and   name  = 'ROUTE_END_AT_WAREHOUSE_FK'
            and   indid > 0
            and   indid < 255)
   drop index ROUTE.ROUTE_END_AT_WAREHOUSE_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ROUTE')
            and   name  = 'ROUTE_FROM_WAREHOUSE_FK'
            and   indid > 0
            and   indid < 255)
   drop index ROUTE.ROUTE_FROM_WAREHOUSE_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ROUTE')
            and   type = 'U')
   drop table ROUTE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRIP')
            and   name  = 'VEHICLE_USE_IN_TRIP_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRIP.VEHICLE_USE_IN_TRIP_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TRIP')
            and   type = 'U')
   drop table TRIP
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRIP_PACKAGE')
            and   name  = 'PACKAGE_TRANSPORT_BY_TRIP_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRIP_PACKAGE.PACKAGE_TRANSPORT_BY_TRIP_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRIP_PACKAGE')
            and   name  = 'TRIP_HAS_PACKAGE_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRIP_PACKAGE.TRIP_HAS_PACKAGE_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TRIP_PACKAGE')
            and   type = 'U')
   drop table TRIP_PACKAGE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRIP_ROUTE')
            and   name  = 'TRIP_TRANSPORT_ROUTE_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRIP_ROUTE.TRIP_TRANSPORT_ROUTE_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TRIP_ROUTE')
            and   name  = 'ROUTE_TRANSPORT_BY_TRIP_FK'
            and   indid > 0
            and   indid < 255)
   drop index TRIP_ROUTE.ROUTE_TRANSPORT_BY_TRIP_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TRIP_ROUTE')
            and   type = 'U')
   drop table TRIP_ROUTE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('VEHICLE')
            and   name  = 'BRANCH_OWN_RENT_VEHICLE_FK'
            and   indid > 0
            and   indid < 255)
   drop index VEHICLE.BRANCH_OWN_RENT_VEHICLE_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VEHICLE')
            and   type = 'U')
   drop table VEHICLE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('WAREHOUSE')
            and   name  = 'BRANCH_HAS_WAREHOUSE_FK'
            and   indid > 0
            and   indid < 255)
   drop index WAREHOUSE.BRANCH_HAS_WAREHOUSE_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('WAREHOUSE')
            and   name  = 'WAREHOUSE_STORE_PACKAGES_FK'
            and   indid > 0
            and   indid < 255)
   drop index WAREHOUSE.WAREHOUSE_STORE_PACKAGES_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('WAREHOUSE')
            and   type = 'U')
   drop table WAREHOUSE
go

/*==============================================================*/
/* Table: ACCOUNT                                               */
/*==============================================================*/
create table ACCOUNT (
   USER_NAME            nvarchar(20)         not null,
   BRANCH_ID            int                  null,
   PASSWORD             nvarchar(20)         not null,
   FULL_NAME            nvarchar(100)        not null,
   ADDRESS              nvarchar(200)        not null,
   PHONE_NUMBER         nvarchar(20)         null,
   EMAIL                nvarchar(40)        UNIQUE,
   IS_BLOCKED           bit                  not null DEFAULT 'FALSE',
   ROLE					nvarchar(30)   		 not null DEFAULT'Employee',
   constraint PK_ACCOUNT primary key nonclustered (USER_NAME)
)
go

/*==============================================================*/
/* Index: ACCOUNT_WORK_AT_BRANCH_FK                             */
/*==============================================================*/
create index ACCOUNT_WORK_AT_BRANCH_FK on ACCOUNT (
BRANCH_ID ASC
)
go

/*==============================================================*/
/* Table: BRANCH                                                */
/*==============================================================*/
create table BRANCH (
   BRANCH_ID            int                  not null IDENTITY (1,1),
   CITY_ID              int                  not null,
   BRANCH_NAME          nvarchar(100)        not null,
   ADDRESS              nvarchar(200)        not null,
   EMAIL                nvarchar(40)         not null,
   PHONE_NUMBER         nvarchar(20)         null,
   IMAGE                nvarchar(150)        null,
   DESCRIPTION          nvarchar(400)        null,
   UNIT_PRICE			money             not null default (50),
   COMMISSION_PERCENT   smallint             not null default (10),
   IS_DELETED	bit              not null  default 'FALSE',
   constraint PK_BRANCH primary key nonclustered (BRANCH_ID)
)
go

/*==============================================================*/
/* Index: CITY_HAS_BRANCH_FK                                    */
/*==============================================================*/
create index CITY_HAS_BRANCH_FK on BRANCH (
CITY_ID ASC
)
go

/*==============================================================*/
/* Table: CITY                                                  */
/*==============================================================*/
create table CITY (
   CITY_ID              int                  not null IDENTITY(1,1),
   CITY_NAME            nvarchar(50)         not null,
   IS_DELETED	bit              not null  default 'FALSE',
   constraint PK_CITY primary key nonclustered (CITY_ID)
)
go

/*==============================================================*/
/* Table: CUSTOMER                                              */
/*==============================================================*/
create table CUSTOMER (
   USER_NAME            nvarchar(20)         not null,
   CITY_ID              int                  not null,
   PASSWORD             nvarchar(20)         not null,
   FULL_NAME            nvarchar(100)        not null,
   DATE_OF_BIRTH        datetime             not null,
   GENDER               nvarchar(8)          not null,
   ADDRESS              nvarchar(200)        not null,
   PHONE_NUMBER         nvarchar(20)         null,
   EMAIL                nvarchar(40)         not null,
   IS_BLOCKED           bit                  null DEFAULT'FALSE',
   constraint PK_CUSTOMER primary key nonclustered (USER_NAME)
)
go

/*==============================================================*/
/* Index: BELONG_TO_HAS_FK                                      */
/*==============================================================*/
create index BELONG_TO_HAS_FK on CUSTOMER (
CITY_ID ASC
)
go

/*==============================================================*/
/* Table: DELIVERY                                              */
/*==============================================================*/
create table DELIVERY (
   DELIVERY_ID          int                  not null IDENTITY(1,1),
   CONSIGNER_ID         nvarchar(20)         not null,
   START_BRANCH_ID      int                  not null,
   DESTINATION_BRANCH_ID int                  not null,
   PICK_UP_ADDRESS      nvarchar(200)        null,
   TRACKING_CODE        nvarchar(10)         null,
   CONSIGNEE_NAME       nvarchar(100)        not null,
   CONSIGNEE_ADDRESS    nvarchar(200)        not null,
   TOTAL_WEIGHT         float               not null,
   RATE                 money     null,
   COMISSION            money     null,
   BOOKED_DATE          datetime           not  null default GetDate(),
   CONFIRMED_DATE       datetime             null,
   PICKUP_DATE          datetime             not null,
   CUSTOMERCC_NUMBER    nvarchar(20)         null,
   DELIVERY_DATE        datetime             null,
   BOOKING_STATUS       nvarchar(20)         not null default 'Pending',
   PUBNISH_RATE         money                null,
   IS_FINISHED          bit                  not null default 'FALSE',
   IS_DELETED	bit              not null  default 'FALSE',
   constraint PK_DELIVERY primary key nonclustered (DELIVERY_ID)
)
go

/*==============================================================*/
/* Index: START_AT_START_POINT_FK                               */
/*==============================================================*/
create index START_AT_START_POINT_FK on DELIVERY (
START_BRANCH_ID ASC
)
go

/*==============================================================*/
/* Index: DELIVERY_HAS_DETINATION_AT_BRANCH_FK                  */
/*==============================================================*/
create index DELIVERY_HAS_DETINATION_AT_BRANCH_FK on DELIVERY (
DESTINATION_BRANCH_ID ASC
)
go

/*==============================================================*/
/* Index: BOOK_BOOK_BY_FK                                       */
/*==============================================================*/
create index BOOK_BOOK_BY_FK on DELIVERY (
CONSIGNER_ID ASC
)
go

/*==============================================================*/
/* Table: PACKAGE                                               */
/*==============================================================*/
create table PACKAGE (
   PACKAGE_ID           int                  not null IDENTITY(1,1),
   DELIVERY_ID          int                  not null,
   WEIGHT               float                 not null,
   DELIVERY_STATUS      nvarchar(20)         not null DEFAULT 'Planned',
   IS_DELETED           bit                  null DEFAULT 'FALSE',
   constraint PK_PACKAGE primary key nonclustered (PACKAGE_ID)
)
go

/*==============================================================*/
/* Index: INCLUDE_BELONG_TO_FK                                  */
/*==============================================================*/
create index INCLUDE_BELONG_TO_FK on PACKAGE (
DELIVERY_ID ASC
)
go

/*==============================================================*/
/* Table: ROUTE                                                 */
/*==============================================================*/
create table ROUTE (
   ROUTE_ID             int                  not null IDENTITY(1,1),
   FROM_BRANCH_ID       int                  not null,
   TO_BRANCH_ID			int                  not null,
   DISTANCE             double precision     not null,
   IS_DELETED           bit                  null default'false',
   constraint PK_ROUTE primary key nonclustered (ROUTE_ID)
)
go

/*==============================================================*/
/* Index: ROUTE_FROM_WAREHOUSE_FK                               */
/*==============================================================*/
create index ROUTE_FROM_WAREHOUSE_FK on ROUTE (
FROM_BRANCH_ID ASC
)
go

/*==============================================================*/
/* Index: ROUTE_END_AT_WAREHOUSE_FK                             */
/*==============================================================*/
create index ROUTE_END_AT_WAREHOUSE_FK on ROUTE (
TO_BRANCH_ID ASC
)
go

/*==============================================================*/
/* Table: TRIP                                                  */
/*==============================================================*/
create table TRIP (
   TRIP_ID              int                  not null IDENTITY(1,1),
   VEHICLE_ID           nvarchar(8)          not null,
   START_DATE           datetime             not null,
   ARRIVE_DATE          datetime             null,
   MAX_ROUTE            smallint             not null,
   IS_FINISHED          bit                  not null default 'FALSE',
   IS_DELETED	bit              not null  default 'FALSE',
   constraint PK_TRIP primary key nonclustered (TRIP_ID)
)
go

/*==============================================================*/
/* Index: VEHICLE_USE_IN_TRIP_FK                                */
/*==============================================================*/
create index VEHICLE_USE_IN_TRIP_FK on TRIP (
VEHICLE_ID ASC
)
go

/*==============================================================*/
/* Table: TRIP_PACKAGE                                          */
/*==============================================================*/
create table TRIP_PACKAGE (
   PACKAGE_ID           int                  not null,
   TRIP_ID              int                  not null,
   IS_UNLOADED          bit                  not null DEFAULT 'true',
   constraint PK_TRIP_PACKAGE primary key nonclustered (PACKAGE_ID, TRIP_ID)
)
go

/*==============================================================*/
/* Index: TRIP_HAS_PACKAGE_FK                                   */
/*==============================================================*/
create index TRIP_HAS_PACKAGE_FK on TRIP_PACKAGE (
TRIP_ID ASC
)
go

/*==============================================================*/
/* Index: PACKAGE_TRANSPORT_BY_TRIP_FK                          */
/*==============================================================*/
create index PACKAGE_TRANSPORT_BY_TRIP_FK on TRIP_PACKAGE (
PACKAGE_ID ASC
)
go

/*==============================================================*/
/* Table: TRIP_ROUTE                                            */
/*==============================================================*/
create table TRIP_ROUTE (
   TRIP_ID              int                  not null ,
   ROUTE_ID             int                  not null,
   ROUTE_ORDER          smallint             not null,
   ARRIVE_DATE          datetime             null,
   STATUS               nvarchar(30)         not null default 'Planned',
   IS_DELETED			bit              not null  default 'FALSE',
   constraint PK_TRIP_ROUTE primary key nonclustered (TRIP_ID, ROUTE_ID)
)
go

/*==============================================================*/
/* Index: ROUTE_TRANSPORT_BY_TRIP_FK                            */
/*==============================================================*/
create index ROUTE_TRANSPORT_BY_TRIP_FK on TRIP_ROUTE (
ROUTE_ID ASC
)
go

/*==============================================================*/
/* Index: TRIP_TRANSPORT_ROUTE_FK                               */
/*==============================================================*/
create index TRIP_TRANSPORT_ROUTE_FK on TRIP_ROUTE (
TRIP_ID ASC
)
go

/*==============================================================*/
/* Table: VEHICLE                                               */
/*==============================================================*/
create table VEHICLE (
   VEHICLE_ID           nvarchar(8)          not null,
   BRANCH_ID            int                  null,
   TYPE                 nvarchar(20)         not null,
   WEIGHT_LIMIT         int                  not null,
   STATUS               nvarchar(20)         not null default'Free',
   IS_DELETED	bit              not null  default 'FALSE',
   constraint PK_VEHICLE primary key nonclustered (VEHICLE_ID)
)
go

/*==============================================================*/
/* Index: BRANCH_OWN_RENT_VEHICLE_FK                            */
/*==============================================================*/
create index BRANCH_OWN_RENT_VEHICLE_FK on VEHICLE (
BRANCH_ID ASC
)
go

/*==============================================================*/
/* Table: WAREHOUSE                                             */
/*==============================================================*/
create table WAREHOUSE (
   BRANCH_ID            int                  not null,
   PACKAGE_ID           int                  not null,
   IS_REMOVED           bit                  not null default 'FALSE',
   constraint PK_WAREHOUSE primary key nonclustered (BRANCH_ID,PACKAGE_ID)
)
go

/*==============================================================*/
/* Index: WAREHOUSE_STORE_PACKAGES_FK                           */
/*==============================================================*/
create index WAREHOUSE_STORE_PACKAGES_FK on WAREHOUSE (
PACKAGE_ID ASC
)
go

/*==============================================================*/
/* Index: BRANCH_HAS_WAREHOUSE_FK                               */
/*==============================================================*/
create index BRANCH_HAS_WAREHOUSE_FK on WAREHOUSE (
BRANCH_ID ASC
)
go

alter table ACCOUNT
   add constraint FK_ACCOUNT_ACCOUNT_W_BRANCH foreign key (BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table BRANCH
   add constraint FK_BRANCH_CITY_HAS__CITY foreign key (CITY_ID)
      references CITY (CITY_ID)
go

alter table CUSTOMER
   add constraint FK_CUSTOMER_BELONG_TO_CITY foreign key (CITY_ID)
      references CITY (CITY_ID)
go

alter table DELIVERY
   add constraint FK_DELIVERY_BOOK_BOOK_CUSTOMER foreign key (CONSIGNER_ID)
      references CUSTOMER (USER_NAME)
go

alter table DELIVERY
   add constraint FK_DELIVERY_DELIVERY__BRANCH foreign key (DESTINATION_BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table DELIVERY
   add constraint FK_DELIVERY_START_AT__BRANCH foreign key (START_BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table PACKAGE
   add constraint FK_PACKAGE_INCLUDE_B_DELIVERY foreign key (DELIVERY_ID)
      references DELIVERY (DELIVERY_ID)
go

alter table ROUTE
   add constraint FK_ROUTE_ROUTE_END_WAREHOUS foreign key (TO_BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table ROUTE
   add constraint FK_ROUTE_ROUTE_FRO_WAREHOUS foreign key (FROM_BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table TRIP
   add constraint FK_TRIP_VEHICLE_U_VEHICLE foreign key (VEHICLE_ID)
      references VEHICLE (VEHICLE_ID)
go

alter table TRIP_PACKAGE
   add constraint FK_TRIP_PAC_PACKAGE_T_PACKAGE foreign key (PACKAGE_ID)
      references PACKAGE (PACKAGE_ID)
go

alter table TRIP_PACKAGE
   add constraint FK_TRIP_PAC_TRIP_HAS__TRIP foreign key (TRIP_ID)
      references TRIP (TRIP_ID)
go

alter table TRIP_ROUTE
   add constraint FK_TRIP_ROU_ROUTE_TRA_ROUTE foreign key (ROUTE_ID)
      references ROUTE (ROUTE_ID)
go

alter table TRIP_ROUTE
   add constraint FK_TRIP_ROU_TRIP_TRAN_TRIP foreign key (TRIP_ID)
      references TRIP (TRIP_ID)
go

alter table VEHICLE
   add constraint FK_VEHICLE_BRANCH_OW_BRANCH foreign key (BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table WAREHOUSE
   add constraint FK_WAREHOUS_BRANCH_HA_BRANCH foreign key (BRANCH_ID)
      references BRANCH (BRANCH_ID)
go

alter table WAREHOUSE
   add constraint FK_WAREHOUS_WAREHOUSE_PACKAGE foreign key (PACKAGE_ID)
      references PACKAGE (PACKAGE_ID)
go
create function GENERATE_TRACKING_CODE(@i int) 
returns char(8) 
as 
begin 
  return 
	(
	char(@i / 2600000 % 26 + 65) + 
	char(@i / 260000 % 26 + 65) + 
	char(@i / 26000 % 26 + 65) + 
    char(@i / 10000 % 26 + 65) + 
	char(@i / 1000 % 10 + 48) + 
    char(@i / 100 % 10 + 48) + 
    char(@i / 10 % 10 + 48) + 
    char(@i % 10 + 48)) 
  end
GO
--Test Function
--select x, dbo.GENERATE_TRACKING_CODE(x) 
--from 
-- (
--    select 9 as x union all 
--    select 99 union all 
--    select 100 union all 
--    select 999 union all 
--    select 1000 union all 
--    select 25999 union all 
--    select 26000 union all 
--    select 51999 union all 
--    select 52000
-- ) x 
GO

create trigger DELIVERY_insert on DELIVERY 
after insert as 
update 
    DELIVERY 
set 
    DELIVERY.TRACKING_CODE = dbo.GENERATE_TRACKING_CODE(DELIVERY.DELIVERY_ID) 
from 
    DELIVERY
inner join 
    inserted on DELIVERY.DELIVERY_ID= inserted.DELIVERY_ID
GO

